package com.example.nfcschool;

import java.util.ArrayList;
import java.util.Collection;
import java.util.Collections;
import java.util.HashMap;
import java.util.Iterator;

import android.content.Context;
import android.database.Cursor;
import android.database.sqlite.SQLiteDatabase;
import android.database.sqlite.SQLiteDatabase.CursorFactory;
import android.database.sqlite.SQLiteOpenHelper;
import android.util.Log;

public class SDBHelper extends SQLiteOpenHelper{
	
	Context context;
	String dbName;
	int dbVersion;
	SQLiteDatabase db;
	
	
	public SDBHelper(Context context, String name, CursorFactory factory,
			int version) {
		super(context, name, factory, version);
		this.context = context;
		this.dbName = name;
		this.dbVersion = version;
	}

	@Override
	public void onCreate(SQLiteDatabase db) {
		this.db = db;
		String sql = "create table if not exists smember(" +
				" irum varchar2(10)," +
				" tel varchar2(15) primary key," +
				" picture varchar2(400)," +
				" grade varchar2(5)," +
				" pw Integer," +
				" birth varchar2(10)," +
				" teach varchar2(5)," +
				" adds varchar2(500)," +
				" sno varchar2(2));";
		String sql2 = " create table if not exists record(" +
				" tel varcahr2(15)," +
				" attend varchar2(5)," +
				" instate varchar2(1)," +
				" intime varchar2(25)," +
				" outstate varchar2(1)," +
				" outtime varchar2(25)," +
				" foreign key(tel) references smember(tel) );" ;
		String sql3 = " create table if not exists maps(" +
				" tel varchar2(15)," +
				" wee varchar2(20)," +
				" kyung varchar2(20)," +
				" cnt Integer," +
				" foreign key(tel) references smember(tel) );";
		db.execSQL(sql);
		db.execSQL(sql2);
		db.execSQL(sql3);
		
		

//		String[] params={"이선미", "0006", "c:/drawable", "6-2", 
//				"1234", "19910119","true","구미","0"};
//		db.execSQL("insert into smember values (?,?,?,?, ?,?,?,?,?)",params);
//
//		String[] params2={"JungByungTaek", "01087065774", "c:/drawable", "6-2",
//				"1234", "19661203","false","서울","2"};
//		db.execSQL("insert into smember values (?,?,?,?, ?,?,?,?,?)",params2);
//
//		String[] params3={"권익현", "0002", "c:/drawable", "6-1",
//				"1234", "19870622","false","경기","3"};
//		
//		db.execSQL("insert into smember values (?,?,?,?, ?,?,?,?,?)",params3);
//
//		String[] params4={"최정현", "0003", "c:/drawable", "6-1", 
//				"1234", "19910130","false","부산","4"};
//		db.execSQL("insert into smember values (?,?,?,?, ?,?,?,?,?)",params4);
//
//		String[] params5={"박주오", "01054501907", "c:/drawable", "6-3", 
//				"1234", "19930101","false","대구","5"};
//		db.execSQL("insert into smember values (?,?,?,?, ?,?,?,?,?)",params5);
//
//		String[] params6={"조성환", "0005", "c:/drawable", "6-3", 
//				"1234", "19881219","false","대전","6"};
//		db.execSQL("insert into smember values (?,?,?,?, ?,?,?,? ,?)",params6);

		
//		String[] p1={"1","true","1","09:00","0","0"};
//		db.execSQL("insert into record(sno,attend,instate,intime,outstate,outtime) values (?,?,?,?,?,?)",p1);
//		String[] p2={"0002","true","1","07:00","0","0"};
//		db.execSQL("insert into record values (?,?,?,?, ?,?)",p2);
//		String[] p3={"0002","false","0","0","2","14:00"};
//		db.execSQL("insert into record values (?,?,?,?, ?,?)",p3);
//		String[] p4={"0003","true","2","10:00","0","0"};
//		db.execSQL("insert into record values (?,?,?,?, ?,?)",p4);
//		
//		String[] p5={"0001","false","0","0","1","18:06"};
//		db.execSQL("insert into record values (?,?,?,?, ?,?)",p5);
//		String[] p6={"0004","false","0","0","1","18:32"};
//		db.execSQL("insert into record values (?,?,?,?, ?,?)",p6);
//		String[] p7={"0005","true","1","07:00","0","0"};
//		db.execSQL("insert into record values (?,?,?,?, ?,?)",p7);
//		String[] p8={"0006","false","0","0","1","18:06"};
//		db.execSQL("insert into record values (?,?,?,?, ?,?)",p8);
		
	
	
	}

	@Override
	public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {
		// TODO Auto-generated method stub

	}
	
	//*****************************************************<< SELECT >>*******************************
	public ArrayList<SmemberVO> select(){
		ArrayList<SmemberVO> al = new ArrayList<SmemberVO>();
		
		//DB를 읽기 전용으로 열기.
		db = getReadableDatabase();
		String sql = "select * from smember where teach=?";
		String[] param = {"false"};
		Cursor c = db.rawQuery(sql,param);
		while(c.moveToNext()){
			SmemberVO d = new SmemberVO();
			
			d.setIrum(c.getString(0));
			d.setTel(c.getString(1));
			d.setPicture(c.getString(2));
			d.setGrade(c.getString(3));
			
			d.setPw(c.getInt(4));
			d.setBirth(c.getString(5));
			d.setTeach(c.getString(6));
			d.setAdds(c.getString(7));
			
			d.setSno(c.getString(8));
			
			al.add(d);
			
		}
		return al;
	}
	
	public SmemberVO select(String tel){
		SmemberVO d= null;
		//DB를 읽기 전용으로 열기.
		db = getReadableDatabase();
		String sql = "select * from smember where tel = ?";
		String[] pa = {tel};
		Cursor c = db.rawQuery(sql, pa);
		while(c.moveToNext()){
			d = new SmemberVO();
			
			d.setIrum(c.getString(0));
			d.setTel(c.getString(1));
			d.setPicture(c.getString(2));
			d.setGrade(c.getString(3));
			d.setPw(c.getInt(4));
			d.setBirth(c.getString(5));
			d.setTeach(c.getString(6));
			d.setAdds(c.getString(7));
			d.setSno(c.getString(8));
		}
		return d;
	}
	
	public ArrayList<ListDataVO> selectList(){
		ArrayList<ListDataVO> al = new ArrayList<ListDataVO>();
		HashMap<String, ListDataVO> map = new HashMap<String, ListDataVO>();
		
		//DB를 읽기 전용으로 열기.
		db = getReadableDatabase();
		String sql = "select m.sno, m.irum, r.instate, r.intime, r.outstate, r.outtime ,m.tel" +
				" from smember m, record r " +
				" where m.sno=r.sno " +
				" and m.teach = ?" +
				" order by sno,outstate";
		String[] param = {"false"};
		Cursor c = db.rawQuery(sql,param);
		String tempSno ="";
		
		while(c.moveToNext()){
			tempSno = c.getString(0);
			ListDataVO d = new ListDataVO();
			d.setSno(tempSno);
			d.setIrum(c.getString(1));
			d.setInstate(c.getString(2));
			d.setIntime(c.getString(3));
			d.setOutstate(c.getString(4));
			d.setOuttime(c.getString(5));
			d.setTel(c.getString(6));		
			map.put(tempSno, d);
		}
		
//		al = new ArrayList<ListDataVO>(map.values());
		ArrayList<String> arr = new ArrayList<String>(map.keySet());
		
		Collections.sort(arr);
		Iterator<String> it = arr.iterator();
		
		while(it.hasNext()){
			al.add(map.get(it.next()));
		}
		
		return al;
	}

	//*****************************************************<< INSERT >>*******************************
	
	public boolean insert(SmemberVO d){
		boolean flag = true;	
		db = getWritableDatabase();
		String[] params={ d.getIrum(), ""+d.getTel(), d.getPicture(), d.getGrade(), 
				""+d.getPw(), d.getBirth(), d.getTeach(),d.getAdds(),d.getSno()};
		
		try{
			db.beginTransaction();
			db.execSQL("insert into smember values (?,?,?,?, ?,?,?,?,?)",params);
			db.setTransactionSuccessful(); 
		}catch(Exception ex){
			flag = false;
			Log.e("Smember insert : ", ex.toString());
		}finally{
			db.endTransaction();
		}
		
		return flag;
	}
	
	public boolean insert(SRecordVO d){
		boolean flag = true;	
		db = getWritableDatabase();
		String[] params={d.getTel(),d.getAttend(),d.getInstate(),d.getIntime(),d.getOutstate(),d.getOuttime()};
		
		try{
			db.beginTransaction();
			db.execSQL("insert into smember values (?,?,?,?,?,?)",params);
			db.setTransactionSuccessful(); 
		}catch(Exception ex){
			flag = false;
			Log.e("SRecord insert : ", ex.toString());
		}finally{
			db.endTransaction();
		}
		
		return flag;
	}
	

	//*****************************************************<< UPDATE >>*******************************
	
	
	public boolean update(SmemberVO d){
		boolean flag = true;	
		db = getWritableDatabase();
		String sql="update smember set irum=?, adds=?," +
				" grade=?, pw=?, birth=?, sno=? where tel=?";
		String[] params={d.getIrum(), d.getAdds(), d.getGrade(),
				 ""+d.getPw(), d.getBirth(),d.getSno(), d.getTel()};
		
		try{
			db.beginTransaction();
			db.execSQL(sql,params);
			db.setTransactionSuccessful(); 
		}catch(Exception ex){
			flag = false;
		}finally{
			db.endTransaction();
		}
		
		return flag;
	}

	
	

	//*****************************************************<< DELETE >>*******************************
	
	
	
	public boolean idcheck(String tel){
		boolean flag = false;
		db = getReadableDatabase();
		String sql = "select tel from smember";
		
		Cursor c = db.rawQuery(sql, null);
		while(c.moveToNext()){
			if(tel.equals(c.getString(0))){
				flag = true;
				return flag;
			}
		}
		return flag;
	}

	public boolean login(String tel,int pw){
		boolean flag = false;
		db = getReadableDatabase();
		String sql = "select pw from smember where tel=?";
		String[] pa = {tel};
		Cursor c = db.rawQuery(sql, pa);
		while(c.moveToNext()){
			if(c.getInt(0) == pw){
				flag = true;
			}
		}
		return flag;
	}
	
	public boolean telchange(String newtel, String oldtel){
		boolean flag = true;	
		db = getWritableDatabase();
		String sql="update smember set tel=? where tel=?";
		String[] params={newtel,oldtel};
		
		try{
			db.beginTransaction();
			db.execSQL(sql,params);
			db.setTransactionSuccessful(); 
		}catch(Exception ex){
			flag = false;
		}finally{
			db.endTransaction();
		}
		return flag;
	}
	
	
}
